﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class CustomerDAL
    {
        private const string SQL_SELECT_WS_CUSTOMER = "select CST_CUSTOMER_ID as CUSTOMER_ID,CST_CUSTOMER_NAME as CUSTOMER_NAME,CST_SEX as SEX,DATE_FORMAT(CST_BIRTHDAY,'%Y-%m-%d') as BIRTHDAY,CST_MOBILE_NUMBER as MOBILE_NUMBER,CST_TELEPHONE as TELEPHONE,CST_EMAIL as EMAIL,CST_CONTACT_ADDRESS as CONTACT_ADDRESS,CST_FOLLOW_STAFF_ID as FOLLOW_STAFF_ID,CST_REGISTER_TIME as REGISTER_TIME,CST_RCRE_STAFF_ID as RCRE_STAFF_ID,CST_RCRE_TIME as RCRE_TIME,CST_LCHG_STAFF_ID as LCHG_STAFF_ID,CST_LCHG_TIME as LCHG_TIME,(case CST_SEX when 0 then '女' else '男' end) as SEX_NAME,(select STF_STAFF_NAME from WS_STAFF where STF_STAFF_ID=CST_FOLLOW_STAFF_ID) as FOLLOW_STAFF_NAME from WS_CUSTOMER where 1=1";
        private const string SQL_INSERT_WS_CUSTOMER = "insert into WS_CUSTOMER(CST_CUSTOMER_ID,CST_CUSTOMER_NAME,CST_SEX,CST_BIRTHDAY,CST_MOBILE_NUMBER,CST_TELEPHONE,CST_EMAIL,CST_CONTACT_ADDRESS,CST_FOLLOW_STAFF_ID,CST_REGISTER_TIME,CST_RCRE_STAFF_ID,CST_RCRE_TIME) values(@CustomerId,@CustomerName,@Sex,@Birthday,@MobileNumber,@Telephone,@EMail,@ContactAddress,@FollowStaffId,@RegisterTime,@LastStaffId,@LastTime)";
        private const string SQL_UPDATE_WS_CUSTOMER = "update WS_CUSTOMER set CST_CUSTOMER_NAME=@CustomerName,CST_SEX=@Sex,CST_BIRTHDAY=@Birthday,CST_MOBILE_NUMBER=@MobileNumber,CST_TELEPHONE=@Telephone,CST_EMAIL=@EMail,CST_CONTACT_ADDRESS=@ContactAddress,CST_LCHG_STAFF_ID=@LastStaffId,CST_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_UPDATE_FOLLOW_STAFF_ID = "update WS_CUSTOMER set CST_FOLLOW_STAFF_ID=@FollowStaffId,CST_LCHG_STAFF_ID=@LastStaffId,CST_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_DELETE_WS_CUSTOMER = "delete from WS_CUSTOMER where 1=1";
        private const string SQL_COUNT_WS_CUSTOMER = "select count(CST_CUSTOMER_ID) from WS_CUSTOMER where 1=1";

        private const string SQL_WHERE_CUSTOMER_ID = " and CST_CUSTOMER_ID=@CustomerId ";
        private const string SQL_WHERE_CUSTOMER_ID_NAME = " and CST_CUSTOMER_ID<>@CustomerId and CST_CUSTOMER_NAME=@CustomerName ";
        private const string SQL_WHERE_CUSTOMER_ID_MOBILE_NUMBER = " and CST_CUSTOMER_ID<>@CustomerId and CST_MOBILE_NUMBER=@MobileNumber ";
        private const string SQL_WHERE_FOLLOW_STAFF_ID = " and CST_FOLLOW_STAFF_ID=@FollowStaffId ";
        private const string SQL_WHERE_CUSTOMER_NAME = " and CST_CUSTOMER_NAME=@CustomerName ";
        private const string SQL_WHERE_CUSTOMER_NAME_LIKE = " and CST_CUSTOMER_NAME like @CustomerName ";
        private const string SQL_WHERE_MOBILE_NUMBER = " and CST_MOBILE_NUMBER=@MobileNumber ";
        private const string SQL_WHERE_MOBILE_NUMBER_LIKE = " and CST_MOBILE_NUMBER like @MobileNumber ";
        private const string SQL_WHERE_REGISTER_TIME = " and CST_REGISTER_TIME between @StartTime and @EndTime ";

        private const string SQL_ORDER_BY_REGISTER_TIME = " order by REGISTER_TIME ";

        private const string PARM_CUSTOMER_ID = "@CustomerId";
        private const string PARM_CUSTOMER_NAME = "@CustomerName";
        private const string PARM_SEX = "@Sex";
        private const string PARM_BIRTHDAY = "@Birthday";
        private const string PARM_MOBILE_NUMBER = "@MobileNumber";
        private const string PARM_TELEPHONE = "@Telephone";
        private const string PARM_EMAIL = "@EMail";
        private const string PARM_CONTACT_ADDRESS = "@ContactAddress";
        private const string PARM_FOLLOW_STAFF_ID = "@FollowStaffId";
        private const string PARM_REGISTER_TIME = "@RegisterTime";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_START_TIME = "@StartTime";
        private const string PARM_END_TIME = "@EndTime";

        Nullable<DateTime> NullDateTime = null;
        private static CustomerDAL instance = new CustomerDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static CustomerDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(QueryCustomerModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_CUSTOMER);
            List<MySqlParameter> parms = new List<MySqlParameter>();

            if (model != null)
            {
                if (model.QueryCustomerId)
                {
                    sbSQL.Append(SQL_WHERE_CUSTOMER_ID);
                    parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
                }
                if (model.QueryStaffId)
                {
                    sbSQL.Append(SQL_WHERE_FOLLOW_STAFF_ID);
                    parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
                }
                if (model.QueryName)
                {
                    if (model.FuzzyName)
                    {
                        sbSQL.Append(SQL_WHERE_CUSTOMER_NAME_LIKE);
                        parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.Name) });
                    }
                    else
                    {
                        sbSQL.Append(SQL_WHERE_CUSTOMER_NAME);
                        parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
                    }
                }
                if (model.QueryMobileNumber)
                {
                    if (model.FuzzyMobileNumber)
                    {
                        sbSQL.Append(SQL_WHERE_MOBILE_NUMBER_LIKE);
                        parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.MobileNumber) });
                    }
                    else
                    {
                        sbSQL.Append(SQL_WHERE_MOBILE_NUMBER);
                        parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
                    }
                }
                if (model.QueryRegisterTime)
                {
                    sbSQL.Append(SQL_WHERE_REGISTER_TIME);
                    parms.Add(new MySqlParameter(PARM_START_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.StartTime });
                    parms.Add(new MySqlParameter(PARM_END_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.EndTime });
                }
            }
            sbSQL.Append(SQL_ORDER_BY_REGISTER_TIME);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public CustomerModel Get(QueryCustomerModel model)
        {
            using (DataSet ds = this.GetDataSet(model))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new CustomerModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        Name = Convert.ToString(t.ItemArray[1]),
                        Sex = Convert.ToInt32(t.ItemArray[2]),
                        Birthday = t.IsNull(3) ? NullDateTime : Convert.ToDateTime(t.ItemArray[3]),
                        MobileNumber = Convert.ToString(t.ItemArray[4]),
                        Telephone = Convert.ToString(t.ItemArray[5]),
                        EMail = Convert.ToString(t.ItemArray[6]),
                        ContactAddress = Convert.ToString(t.ItemArray[7]),
                        FollowStaffId = Convert.ToString(t.ItemArray[8]),
                        RegisterTime = Convert.ToDateTime(t.ItemArray[9]),
                        CreatedStaffId = Convert.ToString(t.ItemArray[10]),
                        CreatedDate = Convert.ToDateTime(t.ItemArray[11]),
                        LastModifiedStaffId = t.IsNull(12) ? string.Empty : Convert.ToString(t.ItemArray[12]),
                        LastModifiedDate = t.IsNull(13) ? NullDateTime : Convert.ToDateTime(t.ItemArray[13])
                    };
                }
                return null;
            }
        }
        public int Insert(DbTransaction trans, CustomerModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_SEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.Sex });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_TELEPHONE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Telephone });
            parms.Add(new MySqlParameter(PARM_EMAIL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EMail });
            parms.Add(new MySqlParameter(PARM_CONTACT_ADDRESS, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ContactAddress });
            parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FollowStaffId });
            parms.Add(new MySqlParameter(PARM_REGISTER_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.RegisterTime });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_CUSTOMER, parms.ToArray());
        }
        public int Update(DbTransaction trans, CustomerModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_SEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.Sex });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_TELEPHONE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Telephone });
            parms.Add(new MySqlParameter(PARM_EMAIL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EMail });
            parms.Add(new MySqlParameter(PARM_CONTACT_ADDRESS, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ContactAddress });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int UpdateFollowStaff(DbTransaction trans, CustomerModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_FOLLOW_STAFF_ID);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_FOLLOW_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FollowStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool NameExist(string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool MobileNumberExist(string mobileNumber)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_MOBILE_NUMBER);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = mobileNumber });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool MobileNumberExist(string id, string mobileNumber)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID_MOBILE_NUMBER);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = mobileNumber });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
